﻿using FileControlOpr;
using Microsoft.VisualBasic;
using SQLToolsCore.SQLProvide;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using DataBaseFactoryLib;

namespace SQLToolsCore
{
    public partial class Controller : Form
    {
        private readonly Login mainForm;
        private readonly BaseDataBaseOpr db = DBHelper.GetInstanse();
        private List<ButtonInfo> buttonInfoLst = BtnInfoOpr.LoadBtnInfoLst();
        private bool showFlag = false, timeStartFlag = false;
        private int startTime = 0;

        private IControllerSQLProvider controllerSQLProvider;

        public Controller(List<string> DBNameLst, Login form1)
        {
            InitializeComponent();
            checkBox1.Checked = true;
            comboBox1.DataSource = DBNameLst;
            mainForm = form1;
            LoadButtom();
            LoadToolStripItem();

            this.progressBar1.Hide();
            this.label1.Hide();
        }

        private void Controller_Shown(object sender, System.EventArgs e)
        {
            if (db.DbType == DataBaseTypeEnum.MYSQL)
            {
                checkBox2.Visible = false;
                checkBox3.Visible = false;
            }
            controllerSQLProvider = new SQLProviderFactory(db.DbType).CreateSQLProvider();
        }

        /// <summary>
        /// 该窗口关闭，程序关闭
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void This_FormClosed(object sender, FormClosedEventArgs e)
        {
            db.Connection.Dispose();
            mainForm.Close();
        }

        #region 加载动态控件

        #region 动态加载常用按钮
        private void LoadButtom()
        {
            this.groupBox2.Controls.Clear();

            var commonBtLst = buttonInfoLst.FindAll(x => x.BaseFlag && x.CommonFlag);

            for (int i = 0; i < commonBtLst.Count; i++)
            {
                Button tempBT = new();
                int btWidth = 80, btHeight = 32;
                tempBT.Text = commonBtLst[i].ButtonName;
                tempBT.Font = new Font("宋体", 9);
                tempBT.Size = new Size(btWidth, btHeight);
                if (commonBtLst.Count > 2)
                {
                    var disVal = (this.groupBox2.Width - btWidth * commonBtLst.Count) / (commonBtLst.Count + 1);
                    //平铺
                    tempBT.Location = new Point(disVal + i * (btWidth + disVal), 21);
                }
                else
                {
                    //按序排列
                    tempBT.Location = new Point(5 + i * (btWidth + 5), 21);
                }
                tempBT.Parent = this.groupBox2;
                toolTip1.SetToolTip(tempBT, commonBtLst[i].TipStr);
                AddEventByObject(tempBT, commonBtLst[i].FuncName);
                this.groupBox2.Controls.Add(tempBT);
            }
        }

        #endregion

        #region 动态加载右键菜单
        private void LoadToolStripItem()
        {
            foreach (var item in buttonInfoLst)
            {
                ToolStripItem toolStripItem = new ToolStripMenuItem
                {
                    Text = item.ButtonName,
                    Size = new Size(168, 24)
                };
                AddEventByObject(toolStripItem, item.FuncName);
                contextMenuStrip1.Items.Add(toolStripItem);
                //toolTip1.SetToolTip(toolStripItem, item.TipStr);
            }
        }
        #endregion

        #region 动态添加事件
        private void AddEventByObject<T>(T obj, string funcName) where T : IComponent
        {
            var method = GetType().GetMethod(funcName, BindingFlags.NonPublic | BindingFlags.Instance);
            var clickEvent = obj.GetType().GetEvent("click", BindingFlags.Public | BindingFlags.Instance | BindingFlags.IgnoreCase);

            var handler = Delegate.CreateDelegate(clickEvent.EventHandlerType, this, method);
            clickEvent.AddEventHandler(obj, handler);
        }
        #endregion

        #endregion

        #region 查询
        private void Query(object sender, EventArgs e)
        {
            if (!checkBox1.Checked && !checkBox2.Checked && !checkBox3.Checked)
            {
                MessageBox.Show("请选择一种查询数据类型!");
                return;
            }

            RetrieveData();
        }

        private void RetrieveData(bool needShowMsg = true)
        {
            if (!checkBox1.Checked && !checkBox2.Checked && !checkBox3.Checked)
            {
                return;
            }

            try
            {
                var srcDt = controllerSQLProvider.RetrieveDataStrucName(textBox1.Text, checkBox1.Checked, checkBox2.Checked, checkBox3.Checked);

                if (srcDt.Rows.Count <= 0 && needShowMsg)
                {
                    MessageBox.Show("未找到对应数据.");
                }

                checkedListBox1.DataSource = srcDt;
                checkedListBox1.DisplayMember = "stName";

                for (int i = 0; i < checkedListBox1.Items.Count; i++)
                {
                    checkedListBox1.SetItemChecked(i, false);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        #endregion

        #region 功能操作

        #region 注册
        private void Register_Click(object sender, EventArgs e)
        {
            SetBackGroundWorkerDoWork(RegisterTableByCheckBox);
        }

        private void RegisterTableByCheckBox()
        {
            if (checkBox2.Checked && checkBox3.Checked)
            {
                throw new Exception("数据注册仅能选择数据表!");
            }

            if (checkedListBox1.CheckedItems.Count <= 0)
            {
                throw new Exception("请选择需注册表名!");
            }

            try
            {
                string erroMsg = string.Empty;
                foreach (DataRowView dr in checkedListBox1.CheckedItems)
                {
                    string tempMsg = string.Empty;
                    if (!RegisterTable(dr["name"].ToString(), out tempMsg) || !InsertIntoFiledsInfo(dr["name"].ToString(), out tempMsg)
                        || !TranslateFields(dr["name"].ToString(), out tempMsg))
                    {
                        erroMsg += "\"" + dr["name"] + "\"注册失败," + tempMsg + "\r";
                    }

                    //更新进度
                    var progressVal = (checkedListBox1.CheckedItems.IndexOf(dr) + 1) * 10000.00 / checkedListBox1.CheckedItems.Count;
                    backgroundWorker1.ReportProgress(Convert.ToInt32(progressVal), progressVal == 10000 ? "end" : "start");
                }
                backgroundWorker1.ReportProgress(0, "");

                MessageBox.Show("执行完成.\r" + erroMsg);
            }
            catch (Exception ex)
            {
                backgroundWorker1.ReportProgress(0, "end");
                MessageBox.Show(ex.Message);
            }
        }

        //注册
        private bool RegisterTable(string tableName, out string tempMsg)
        {
            #region sql
            string sql = @"DECLARE @table_name NVARCHAR(256),
		    @id0_field NVARCHAR(32),
		    @sql NVARCHAR(MAX);

            SET @table_name = '{0}'

            SELECT @id0_field = id0_field FROM dbo.ZY_T_D_TablesInfo
            WHERE table_name = @table_name;

            IF @id0_field IS NULL
	            BEGIN
	                SELECT @id0_field = a.name FROM sys.columns a
		            JOIN sys.index_columns b ON b.object_id = a.object_id AND b.key_ordinal = a.column_id
		            WHERE a.object_id = OBJECT_ID(@table_name);
	
		            INSERT INTO dbo.ZY_T_D_TablesInfo
		            (table_id,table_name,table_alias_name,table_type,has_id0,id0_field,cur_id0,begin_no,step)
		            VALUES
		            ((SELECT ISNULL(MAX(table_id),0) FROM dbo.ZY_T_D_TablesInfo) + 1,
		            @table_name,@table_name,1,1,ISNULL(@id0_field,'id0'),0,1,1);

		            UPDATE dbo.ZY_T_D_TablesInfo
		            SET cur_id0 = (SELECT ISNULL(MAX(table_id),0) FROM dbo.ZY_T_D_TablesInfo)
		            WHERE table_name = 'ZY_T_D_TablesInfo';

		            SET @sql = 'UPDATE dbo.ZY_T_D_TablesInfo
		            SET cur_id0 = (SELECT ISNULL(MAX(' + @id0_field + '),0) FROM ' + @table_name + ')
		            WHERE table_name = ''' + @table_name + '''';
                    
					EXEC(@sql);
	            END
            ELSE
	            BEGIN
		            SET @sql = 'UPDATE dbo.ZY_T_D_TablesInfo
		            SET cur_id0 = (SELECT ISNULL(MAX(' + @id0_field + '),0) FROM ' + @table_name + ')
		            WHERE table_name = ''' + @table_name + '''';
                    
					EXEC(@sql);
	            END";
            #endregion

            tempMsg = string.Empty;
            try
            {
                db.ExecuteNonQuery(string.Format(sql, tableName));
                return true;
            }
            catch (Exception ex)
            {
                tempMsg = ex.Message;
                return false;
            }
        }

        //插入字段表
        private bool InsertIntoFiledsInfo(string tableName, out string tempMsg)
        {
            #region sql
            string sql = @"INSERT INTO ZY_T_D_FieldsInfo
(field_id, field_name, field_alias_name, table_name, field_label_name, field_format, field_type, field_vartype, field_length, 
is_valid, has_default_value, is_editable, is_nullable, is_required, field_scale, field_precision, field_disp_width, is_enum, rela_catelog, field_unit)
SELECT (SELECT ISNULL(MAX(field_id),0) FROM ZY_T_D_FieldsInfo)+ROW_NUMBER()OVER(ORDER BY tb.column_id),
CAST(tb.字段名 AS VARCHAR(64)),CAST(CASE WHEN tb.别名 = '' THEN tb.字段名 ELSE tb.别名 END AS VARCHAR(64)),
'{0}','#'+CAST(UPPER(tb.字段名)AS VARCHAR(64)),
CASE WHEN tb.data_type = 'int' THEN '%d'WHEN tb.data_type IN ('decimal','numeric') THEN '%f' ELSE '' END,
0,0,
CAST(CASE WHEN ascale > 0 THEN tb.precision+tb.ascale ELSE tb.max_length END AS INT),1,
CASE WHEN tb.默认值 = '' THEN 0 ELSE 1 END,1,
CASE WHEN tb.空否 = 'Y' THEN 0 ELSE 1 END,1,0,0,
CASE WHEN tb.data_type = 'int' THEN 40 ELSE 80 END,NULL,NULL,NULL
FROM (SELECT tb.column_id,tb.name 字段名,tb.data_type,tb.max_length,tb.precision,tb.ascale,
                   CASE
                       WHEN (tb.is_ansi_padded = 1
                            OR tb.scale > 0 
					        AND tb.data_type <> 'datetime') THEN
                           UPPER(tb.data_type) + '(' + tb.col_len + ')'
                       ELSE
                           UPPER(tb.data_type)
                   END 数据类型,
                   tb.is_null 空否,
                   CASE
                       WHEN tb.default_val IS NULL THEN
                           ''
                       WHEN LEFT(tb.default_val, 1) = '''' THEN
                           RIGHT(tb.default_val, LEN(tb.default_val) - 1)
                       ELSE
                           tb.default_val
                   END 默认值,
                   ISNULL(tb.value, '') 别名,
                   '' 备注
            FROM
            (
                SELECT a.column_id,
                       a.name,
                       c.scale,
                       c.name data_type,
                       SUBSTRING(d.definition, 3, LEN(d.definition) - 4) default_val,
                       CASE
                           WHEN a.is_ansi_padded = 1 THEN
                               CAST(a.max_length AS NVARCHAR(8))
                           ELSE
                               CAST(a.precision AS NVARCHAR(8)) + ',' + CAST(a.scale AS NVARCHAR(8))
                       END col_len,a.max_length,a.precision,a.scale ascale,
                       a.is_ansi_padded,
                       CASE
                           WHEN a.is_nullable = 1 THEN
                               'Y'
                           ELSE
                               'N'
                       END is_null,
                       b.value
                FROM sys.columns a
                    LEFT OUTER JOIN sys.extended_properties b
                        ON b.major_id = a.object_id
                           AND b.minor_id = a.column_id
                           AND b.name = 'MS_Description'
                    LEFT OUTER JOIN sys.types c
                        ON a.system_type_id = c.system_type_id
                    LEFT OUTER JOIN sys.default_constraints d
                        ON a.default_object_id = d.object_id
                WHERE a.object_id = OBJECT_ID('{0}')
                      AND c.name <> 'SYSNAME'
            ) tb)tb
WHERE NOT EXISTS (SELECT * FROM ZY_T_D_FieldsInfo fiTb
WHERE fiTb.table_name = '{0}' AND fiTb.field_name = tb.字段名);";
            #endregion

            tempMsg = string.Empty;
            try
            {
                db.ExecuteNonQuery(string.Format(sql, tableName));
                return true;
            }
            catch (Exception ex)
            {
                tempMsg = ex.Message;
                return false;
            }
        }

        //汉化
        private bool TranslateFields(string tableName, out string tempMsg)
        {
            #region sql

            string sql = @" UPDATE a
            SET a.field_alias_name = b.value
            FROM ZY_T_D_FieldsInfo a
            JOIN (SELECT CAST(a.name AS VARCHAR(32))name,
			CASE WHEN CHARINDEX('(',CAST(b.value AS VARCHAR(32))) <=0 THEN CAST(b.value AS VARCHAR(32)) 
			ELSE LEFT(CAST(b.value AS VARCHAR(32)),CHARINDEX('(',CAST(b.value AS VARCHAR(32))) - 1) END value
            FROM sys.columns a
            LEFT OUTER JOIN sys.extended_properties b
            ON b.major_id = a.object_id AND b.minor_id = a.column_id AND b.name = 'MS_Description'
            LEFT OUTER JOIN sys.types c
            ON a.system_type_id = c.system_type_id
            WHERE a.object_id = OBJECT_ID('{0}')
            AND c.name <> 'SYSNAME' AND CAST(b.value AS VARCHAR(32)) IS NOT NULL)b ON a.field_name = b.name
            WHERE a.table_name = '{0}'";

            #endregion

            tempMsg = string.Empty;
            try
            {
                db.ExecuteNonQuery(string.Format(sql, tableName));
                return true;
            }
            catch (Exception ex)
            {
                tempMsg = ex.Message;
                return false;
            }
        }

        #endregion

        #region 删除
        private void Delete_Click(object sender, EventArgs e)
        {
            if (checkedListBox1.CheckedItems.Count <= 0)
            {
                MessageBox.Show("请选择删除的对象!");
                return;
            }
            var inputStr = Interaction.InputBox("若想删除对应内容，请输入【确认删除】。", "确认删除所选内容？");

            //DialogResult dialogResult = MessageBox.Show("确认删除所选内容？", "确认", MessageBoxButtons.OKCancel, MessageBoxIcon.Question);
            if (inputStr == "确认删除")
            {
                string SQLStr = string.Empty;

                try
                {
                    foreach (DataRowView dr in checkedListBox1.CheckedItems)
                    {
                        var tempType = dr["type"].ToString().Trim();
                        if (tempType == "U")
                        {
                            SQLStr += "DROP TABLE " + dr["name"].ToString().Trim() + ";";
                        }
                        else if (tempType == "P")
                        {
                            SQLStr += "DROP PROCEDURE " + dr["name"].ToString().Trim() + ";";
                        }
                        else
                        {
                            SQLStr += "DROP FUNCTION " + dr["name"].ToString().Trim() + ";";
                        }
                    }

                    string erroMsg = string.Empty;
                    try
                    {
                        db.ExecuteNonQuery(SQLStr);
                    }
                    catch (Exception ex)
                    {
                        erroMsg = ex.Message;
                    }

                    if (!string.IsNullOrWhiteSpace(erroMsg))
                    {
                        MessageBox.Show("执行出错.\r" + erroMsg);
                    }
                    else
                    {
                        MessageBox.Show("执行完成.");
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }

                RetrieveData();
            }
            else
            {
                MessageBox.Show("输入字符'" + inputStr + "'与'确认删除'不符，已取消！");
            }
        }
        #endregion

        #region 生成
        private void GeneratingCode_Click(object sender, EventArgs e)
        {
            if (checkBox2.Checked && checkBox3.Checked)
            {
                MessageBox.Show("生成底层代码仅能选择数据表!");
                return;
            }

            if (checkedListBox1.CheckedItems.Count <= 0)
            {
                MessageBox.Show("请选择需生成底层代码的表名!");
                return;
            }
            else if (checkedListBox1.CheckedItems.Count > 1)
            {
                MessageBox.Show("生成basedata层代码时,只能选择一个表!");
            }

            try
            {
                string erroMsg = string.Empty;
                foreach (DataRowView dr in checkedListBox1.CheckedItems)
                {
                    var tempDt = GetTableStructue(dr["name"].ToString(), true);

                    ProductCode(tempDt);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #region 根据模版生成代码

        private void ProductCode(DataTable dt)
        {
            var tableInfo = TransTableInfo(dt);

            //获取模版文件
            var tempPath = Application.StartupPath + "\\template\\";
            var files = Directory.GetFiles(tempPath);

            if (!Directory.Exists(Application.StartupPath + "\\code\\"))
            {
                Directory.CreateDirectory(Application.StartupPath + "\\code\\");
            }
            var innerFiles = Directory.GetFiles(Application.StartupPath + "\\code\\");
            foreach (var file in innerFiles)
            {
                File.Delete(file);
            }

            foreach (var item in files)
            {
                var tempInfo = GetTempLateInfo(item);

                var code = ReplaceTemplate(tempInfo, tableInfo);

                var sw = new StreamWriter(Application.StartupPath + "\\code\\" + Path.GetFileName(item), false);
                sw.WriteLine(code);
                sw.Close();
            }

            Process.Start("explorer.exe", Application.StartupPath + "code");
        }

        /// <summary>
        /// 获取模版信息
        /// </summary>
        /// <param name="filePath">文件路径</param>
        /// <returns></returns>
        private static string GetTempLateInfo(string filePath)
        {
            string rtnStr = string.Empty, line = string.Empty;

            if (!File.Exists(filePath))
            {
                throw new Exception("文件路径缺失");
            }

            using (StreamReader sr = new(filePath))
            {
                while ((line = sr.ReadLine()) != null)
                {
                    rtnStr += line + "\r\n";
                }
            }

            return rtnStr;
        }

        #region 替换模版内容

        /// <summary>
        /// 替换模版内容
        /// </summary>
        /// <param name="templateInfo">模版内容</param>
        /// <returns></returns>
        private string ReplaceTemplate(string templateInfo, TableInfo tableInfo)
        {
            //替换配置标签
            templateInfo = ReplaceConfigSign(templateInfo);

            //替换循环标签
            templateInfo = ReplaceLoopInfo(templateInfo, tableInfo);

            //替换剩余标签
            templateInfo = RepalceTemplateByPropertie(templateInfo, tableInfo, new string[] { "DataStrucs" }.ToList());

            return templateInfo;
        }

        #region 替换循环内容

        /// <summary>
        /// 替换循环部分内容
        /// </summary>
        /// <param name="repeatInfo"></param>
        /// <param name="tableInfo"></param>
        /// <returns></returns>
        private string ReplaceLoopInfo(string templateInfo, TableInfo tableInfo)
        {
            while (templateInfo.IndexOf("&loop&") > 0)
            {
                var repeatInfo = GetInnerTemplateBySign(templateInfo, "loop");

                templateInfo = templateInfo.Replace(repeatInfo,
                    ReplaceLoopInnerInfo(ClearSign(repeatInfo, "loop"), tableInfo));
            }

            return templateInfo;
        }

        /// <summary>
        /// 替换循环内部分内容
        /// </summary>
        /// <param name="repeatInfo"></param>
        /// <param name="tableInfo"></param>
        /// <returns></returns>
        private static string ReplaceLoopInnerInfo(string repeatInfo, TableInfo tableInfo)
        {
            var rtnVal = string.Empty;

            foreach (var item in tableInfo.DataStrucs)
            {
                var tempInfo = repeatInfo;
                if (tempInfo.Contains("&date&"))
                {
                    if (item.CodeDataType == "DateTime")
                        tempInfo = GetInnerTemplateBySign(tempInfo, "date", true);
                    else
                        tempInfo = GetInnerTemplateBySign(tempInfo, "notdate", true);
                }
                tempInfo = RepalceTemplateByPropertie(tempInfo, item);
                rtnVal += tempInfo;
            }

            return rtnVal;
        }

        #endregion

        #region 替换配置标签
        private static string ReplaceConfigSign(string templateInfo)
        {
            var confSignLst = GetConfigSignFromTemplate(templateInfo);
            foreach (var item in confSignLst)
            {
                var replaceStr = string.Empty;
                if (ConfigurationManager.AppSettings.AllKeys.Contains(item))
                {
                    replaceStr = ConfigurationManager.AppSettings[item];
                }

                templateInfo = templateInfo.Replace("&config." + item + "&", replaceStr);
            }

            return templateInfo;
        }

        private static List<string> GetConfigSignFromTemplate(string tempInfo)
        {
            var rtnLst = new List<string>();

            var confSign = "&config.";

            while (tempInfo.Contains(confSign))
            {
                var rtnVal = tempInfo[(tempInfo.IndexOf(confSign) + confSign.Length)..];
                rtnVal = rtnVal.Substring(0, rtnVal.IndexOf("&"));

                rtnLst.Add(rtnVal);

                tempInfo = tempInfo.Replace(confSign + rtnVal + "&", "");
            }

            return rtnLst;
        }

        #endregion

        #region 通用方法

        /// <summary>
        /// 根据标签获取标签内模版
        /// </summary>
        /// <param name="tempInfo"></param>
        /// <param name="sign"></param>
        /// <param name="clearFlag"></param>
        /// <returns></returns>
        private static string GetInnerTemplateBySign(string tempInfo, string sign, bool clearFlag = false)
        {
            tempInfo = tempInfo.Substring(tempInfo.IndexOf("&" + sign + "&"),
                    tempInfo.IndexOf("&/" + sign + "&") - tempInfo.IndexOf("&" + sign + "&") + sign.Length + 3);

            if (clearFlag)
                tempInfo = ClearSign(tempInfo, sign);

            return tempInfo;
        }

        /// <summary>
        /// 清楚标签
        /// </summary>
        /// <param name="tempInfo"></param>
        /// <param name="sign"></param>
        /// <returns></returns>
        private static string ClearSign(string tempInfo, string sign)
        {
            tempInfo = tempInfo.Replace("&" + sign + "&", "").Replace("&/" + sign + "&", "");

            return tempInfo;
        }

        /// <summary>
        /// 根据对象属性替换模版中替换标签
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="tempInfo"></param>
        /// <param name="tableInfo"></param>
        /// <param name="exceptProperties">除外属性名</param>
        /// <returns></returns>
        private static string RepalceTemplateByPropertie<T>(string tempInfo, T tableInfo, List<string> exceptProperties = null)
        {
            var t = typeof(T);
            PropertyInfo[] properties = t.GetProperties(BindingFlags.Instance | BindingFlags.Public);

            foreach (var propertie in properties)
            {
                if (exceptProperties != null && exceptProperties.Contains(propertie.Name))
                    continue;

                tempInfo = tempInfo.Replace("{{" + propertie.Name + "}}", propertie.GetValue(tableInfo).ToString());
            }

            return tempInfo;
        }

        #endregion

        #endregion

        #endregion

        #endregion

        #region 添加数据
        private void AddData_Click(object sender, EventArgs e)
        {
            if (checkBox2.Checked && checkBox3.Checked)
            {
                MessageBox.Show("添加数据仅能选择数据表!");
                return;
            }

            if (checkedListBox1.CheckedItems.Count <= 0)
            {
                MessageBox.Show("请选择需添加数据的表名!");
                return;
            }
            else if (checkedListBox1.CheckedItems.Count > 1)
            {
                MessageBox.Show("添加数据时,暂时只能选择一个表!");
            }

            try
            {
                string erroMsg = string.Empty;

                foreach (DataRowView dr in checkedListBox1.CheckedItems)
                {
                    var tempDt = GetTableStructue(dr["name"].ToString());

                    DataAdder dataAdderForm = new(tempDt, dr["name"].ToString());
                    dataAdderForm.Show();
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        #endregion

        #region 导出数据结构
        private void Export_Click(object sender, EventArgs e)
        {
            if (checkBox2.Checked && checkBox3.Checked)
            {
                MessageBox.Show("导出结构仅能选择数据表!");
                return;
            }

            if (checkedListBox1.CheckedItems.Count <= 0)
            {
                MessageBox.Show("请选择需导出结构的表名!");
                return;
            }

            var savePath = GetSaveFilePath("数据结构");

            if (string.IsNullOrEmpty(savePath))
            {
                return;
            }

            var rtnDs = GetTableInfo();

            try
            {
                if (Path.GetExtension(savePath) == ".xlsx")
                {
                    ExcelOpr.DataSetToSignSheetExcel(rtnDs, savePath, 1, true);
                    backgroundWorker1.ReportProgress(10000, "end");
                }
                else
                {
                    MessageBox.Show("由于.NETCore中Docx类库效果不好，未找寻替代方案，暂时不支持word导出。");
                    return;

                    DataSetToWord(rtnDs, savePath);
                }
                MessageBox.Show("导出成功！");

                savePath.OpenFile();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void DataSetToWord(DataSet ds, string savePath)
        {
            var doc = DocXOpr.Create(savePath);
            for (int i = 0; i < ds.Tables.Count; i++)
            {
                DocXOpr.InsertParagraph(doc, (i + 1).ToString() + ". " + ds.Tables[i].TableName, 14, true);
                DocXOpr.InsertTableByDataTable(doc, ds.Tables[i], Color.Gainsboro, 0, true, 11);
                DocXOpr.InsertParagraph(doc, "");
                var progressVal = 5000 + ((i + 1) * 5000 / ds.Tables.Count);
                backgroundWorker1.ReportProgress(progressVal, "start");
            }
            doc.Save();
        }

        #region 循环获取所选表的结构DataTable
        private DataSet GetTableInfo()
        {
            var rtnDs = new DataSet();

            foreach (DataRowView dr in checkedListBox1.CheckedItems)
            {
                var tableName = dr["name"].ToString();
                var dt = GetTableStructue(tableName);

                if (dt != null)
                {
                    dt.Columns.Remove("序号");
                    dt.TableName = "表名：" + tableName;
                    rtnDs.Tables.Add(dt.Copy());
                }
                int progressVal = (checkedListBox1.CheckedItems.IndexOf(dr) + 1) * 5000 / checkedListBox1.CheckedItems.Count;
                backgroundWorker1.ReportProgress(progressVal, "start");
            }

            return rtnDs;
        }
        #endregion

        /// <summary>
        /// 获取要保存的文件名称（含完整路径）
        /// </summary>
        /// <returns></returns>
        private static string GetSaveFilePath(string fileName = null)
        {
            SaveFileDialog saveFileDig = new();
            if (!string.IsNullOrEmpty(fileName))
                saveFileDig.FileName = fileName;
            saveFileDig.Filter = "Excel(*.xlsx)|.xlsx|Word(*.docx)|.docx";
            saveFileDig.FilterIndex = 0;
            saveFileDig.OverwritePrompt = true;
            //saveFileDig.InitialDirectory = Common.DesktopDirectory;
            string filePath = null;
            if (saveFileDig.ShowDialog() == DialogResult.OK)
            {
                filePath = saveFileDig.FileName;
            }

            return filePath;
        }
        #endregion

        #region 查看数据结构
        private void ShowStuc_Click(object sender, EventArgs e)
        {
            if (checkBox2.Checked && checkBox3.Checked)
            {
                MessageBox.Show("添加数据仅能选择数据表!");
                return;
            }

            if (checkedListBox1.CheckedItems.Count <= 0)
            {
                MessageBox.Show("请选择需添加数据的表名!");
                return;
            }

            try
            {
                string erroMsg = string.Empty;

                foreach (DataRowView dr in checkedListBox1.CheckedItems)
                {
                    var tempDt = GetTableStructue(dr["name"].ToString());
                    if (tempDt != null)
                    {
                        var strucShowerForm = new StructureShower(tempDt);
                        strucShowerForm.Show();
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        #endregion

        #region 导入数据结构
        private void ImportStuc_Click(object sender, EventArgs e)
        {
            try
            {
                var path = string.Empty;
                OpenFileDialog fileDialog = new()
                {
                    Multiselect = true,
                    Title = "请选择文件",
                    Filter = "所有文件(*xls*)|*.xls*" //设置要选择的文件的类型
                };
                if (fileDialog.ShowDialog() == DialogResult.OK)
                {
                    path = fileDialog.FileName;//返回文件的完整路径       
                    var ds = ExcelOpr.ExcelToDataTableByTiltle(path, "表名：", 0);
                    foreach (DataTable item in ds.Tables)
                    {
                        item.TableName = item.TableName.Replace("表名：", "");
                        DealTableInfo(TransTableInfo(item));
                    }

                    MessageBox.Show("更新成功！");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void DealTableInfo(TableInfo obj)
        {
            //先获取目前对应表对象
            var oriTableInfo = GetTableInfo(obj.TableName);

            //不存在表添加
            if (oriTableInfo == null)
            {
                return;
            }
            else
            {
                foreach (var item in obj.DataStrucs)
                {
                    var oriStruc = oriTableInfo.DataStrucs.FirstOrDefault(x => x.FiledName == item.FiledName);

                    #region 无对应字段添加该字段并增加说明
                    if (oriStruc == null)
                    {
                        string sql = @"ALTER TABLE {0} ADD {1} {2} ";
                        sql = string.Format(sql, obj.TableName, item.FiledName, item.DataType);

                        if (!string.IsNullOrWhiteSpace(item.DefaultValue))
                        {
                            sql += " DEFAULT " + "'" + item.DefaultValue + "'";
                        }

                        db.ExecuteNonQuery(sql);

                        if (string.IsNullOrWhiteSpace(oriStruc.AliasName) && string.IsNullOrWhiteSpace(oriStruc.Remark))
                        {
                            sql = @"EXEC sp_addextendedproperty N'MS_Description', N'{2}', N'SCHEMA', N'dbo', N'TABLE', N'{0}', N'COLUMN', N'{1}'";
                        }
                        else
                        {
                            sql = @"EXEC sp_updateextendedproperty N'MS_Description',N'{2}',N'SCHEMA',N'dbo','TABLE',N'{0}',N'COLUMN', N'{1}'";
                        }

                        sql = string.Format(sql, obj.TableName, item.FiledName, item.AliasName + item.Remark);
                        db.ExecuteNonQuery(sql);
                    }
                    #endregion

                    //有的该字段校对各属性是否一致
                    else
                    {
                        #region 如果数据类型不同
                        if (oriStruc.DataType != item.DataType)
                        {

                        }
                        #endregion 

                        #region 空否条件不同
                        if (oriStruc.AliasName != item.AliasName)
                        {

                        }
                        #endregion 

                        #region 默认值不同
                        if (oriStruc.DefaultValue != item.DefaultValue)
                        {

                        }
                        #endregion 

                        #region 如果别名或备注不一致
                        if (oriStruc.AliasName != item.AliasName || oriStruc.Remark != item.Remark)
                        {
                            string sql = string.Empty;
                            if (string.IsNullOrWhiteSpace(oriStruc.AliasName) && string.IsNullOrWhiteSpace(oriStruc.Remark))
                            {
                                sql = @"EXEC sp_addextendedproperty N'MS_Description', N'{2}', N'SCHEMA', N'dbo', N'TABLE', N'{0}', N'COLUMN', N'{1}'";
                            }
                            else
                            {
                                sql = @"EXEC sp_updateextendedproperty N'MS_Description',N'{2}',N'SCHEMA',N'dbo','TABLE',N'{0}',N'COLUMN', N'{1}'";
                            }

                            sql = string.Format(sql, obj.TableName, item.FiledName, item.AliasName + item.Remark);
                            db.ExecuteNonQuery(sql);
                        }
                        #endregion 
                    }
                }

                #region 多出的值，删除
                var otherStr = oriTableInfo.DataStrucs.Select(x => x.FiledName).ToList()
                    .FindAll(x => !obj.DataStrucs.Exists(y => y.FiledName == x));
                foreach (var item in otherStr)
                {
                    //如果有默认值约束，释放默认值
                    string defaultSQL = @"SELECT name
                    FROM sys.default_constraints
                    WHERE parent_object_id = OBJECT_ID('{0}')
                    AND parent_column_id = COLUMNPROPERTY(OBJECT_ID('{0}'),  '{1}','ColumnId')";
                    defaultSQL = string.Format(defaultSQL, obj.TableName, item);
                    var scalar = db.ExecuteScalar<string>(defaultSQL);
                    if (scalar != null)
                    {
                        string delSQL = @"alter table {0} drop constraint {1}";
                        delSQL = string.Format(delSQL, obj.TableName, scalar.ToString());
                        db.ExecuteNonQuery(delSQL);
                    }

                    string sql = @"alter table {0} drop column {1};";
                    sql = string.Format(sql, obj.TableName, item);
                    db.ExecuteNonQuery(sql);
                }
                #endregion
            }
        }
        #endregion

        #region 清除所选表数据
        private void TruncateTable_Click(object sender, EventArgs e)
        {
            if (checkBox2.Checked && checkBox3.Checked)
            {
                MessageBox.Show("添加数据仅能选择数据表!");
                return;
            }

            if (checkedListBox1.CheckedItems.Count <= 0)
            {
                MessageBox.Show("请选择需清楚数据的表名!");
                return;
            }
            var inputStr = Interaction.InputBox("若想清楚对应表数据，请输入【确认清除】。", "确认清除所选内容？");
            if (inputStr == "确认清除")
            {
                try
                {
                    string erroMsg = string.Empty;

                    string sql = string.Empty;
                    foreach (DataRowView dr in checkedListBox1.CheckedItems)
                    {
                        var tempDt = GetTableStructue(dr["name"].ToString());
                        if (tempDt != null)
                        {
                            sql += "TRUNCATE TABLE " + tempDt.TableName + @";
                            ";
                        }
                    }

                    db.ExecuteNonQuery(sql);

                    MessageBox.Show("清除成功！");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else
            {
                MessageBox.Show("输入字符'" + inputStr + "'与'确认清除'不符，已取消！");
            }
        }
        #endregion

        #region 设置生成代码模版
        private void SetProduceCodeTemplate_Click(object sender, EventArgs e)
        {
            System.Diagnostics.Process.Start("template", Application.StartupPath);
        }
        #endregion

        #region 设置常用按钮
        private void SetCommonButton_Click(object sender, EventArgs e)
        {
            var setCommBtn = new SetCommonBtn(buttonInfoLst);
            if (setCommBtn.ShowDialog() == DialogResult.OK)
            {
                buttonInfoLst = setCommBtn.btnInfoLst;
                LoadButtom();
                BtnInfoOpr.SaveBtnInfoLst(buttonInfoLst);
                setCommBtn.Close();
            }
        }
        #endregion

        private void Default_Click(object sender, EventArgs e)
        {
            MessageBox.Show("该功能仍在开发中...");
        }

        private void MouseEnterEvent(object sender, EventArgs e)
        {
            int endTime = Environment.TickCount;

            if (!timeStartFlag)
            {
                startTime = Environment.TickCount;
                timeStartFlag = true;
            }
            if (!showFlag && endTime - startTime > 5)
            {
                checkedListBox1.Hide();
            }
        }

        private void Live(object sender, EventArgs e)
        {
            startTime = 0;
            timeStartFlag = false;
            showFlag = false;
            checkedListBox1.Show();
        }

        #region 设置异步执行并登记进度的方法
        private void SetBackGroundWorkerDoWork(BtnFunc btnFunc)
        {
            backgroundWorker1 = new BackgroundWorker
            {
                WorkerReportsProgress = true
            };
            backgroundWorker1.DoWork += (o, ea) =>
            {
                BackgroundWorkerDoWork(btnFunc);
            };
            backgroundWorker1.ProgressChanged += new ProgressChangedEventHandler(BgworkerProgessChanged);
            backgroundWorker1.RunWorkerAsync();
        }
        #endregion

        #endregion

        #region 获取数据结构对象或DT

        #region 生成表结构对象
        private TableInfo TransTableInfo(DataTable dt)
        {
            var rtnInfo = new TableInfo
            {
                //表名取结尾存起来
                TableName = dt.TableName,
                DataStrucs = db.FindByDataTable<DataStruc>(dt)
            };

            TransTableInfo(rtnInfo);

            return rtnInfo;
        }

        /// <summary>
        /// 根据基础信息实体补充生成相关属性数据
        /// </summary>
        /// <param name="rtnInfo">基本信息实体</param>
        public static void TransTableInfo(TableInfo rtnInfo)
        {
            rtnInfo.LastTableName = rtnInfo.TableName[(rtnInfo.TableName.LastIndexOf("_") + 1)..];
            rtnInfo.LowerTableName = rtnInfo.LastTableName.ToLower();
            rtnInfo.FLLastTableName = rtnInfo.LastTableName.Substring(0, 1).ToUpper() + rtnInfo.LastTableName[1..];

            int index = 0;
            foreach (var item in rtnInfo.DataStrucs)
            {
                item.Index = index++;

                var filedNameArray = item.FiledName.Split('_');

                for (int i = 0; i < filedNameArray.Length; i++)
                {
                    //FiledNameMember用驼峰法记录字段名
                    if (i == 0)
                        item.FiledNameMember += filedNameArray[i];
                    else
                        item.FiledNameMember += filedNameArray[i].Substring(0, 1).ToUpper() + filedNameArray[i][1..];

                    //FiledNamePropertie用首字母大小法记录字段名
                    item.FiledNamePropertie += filedNameArray[i].Substring(0, 1).ToUpper() + filedNameArray[i][1..];
                }

                var dtAndDv = GetDeflautValByDataType(item.DataType);

                item.CodeDataType = dtAndDv[0];

                if (!string.IsNullOrWhiteSpace(item.DefaultValue))
                    item.CodeDeflautVal = item.DefaultValue;
                else
                    item.CodeDeflautVal = dtAndDv[1];


                item.PrimaryKeySign = item.PrimaryKey == "Y" ? "Id" : "Column";

                if (!string.IsNullOrWhiteSpace(item.Remark))
                {
                    item.Remark = "(" + item.Remark + ")";
                }
            }
        }

        /// <summary>
        /// 根据数据类型获取代码中值类型及默认值
        /// </summary>
        /// <param name="dataType">数据类型</param>
        /// <returns></returns>
        private static string[] GetDeflautValByDataType(string dataType)
        {
            var rtnVal = new string[2];

            if (dataType.Contains("("))
                dataType = dataType.Substring(0, dataType.IndexOf("("));

            dataType = dataType.ToLower();

            switch (dataType)
            {
                #region 数字型
                case "bigint":
                    rtnVal[0] = "long";
                    rtnVal[1] = "0";
                    break;
                case "int":
                    rtnVal[0] = "int";
                    rtnVal[1] = "0";
                    break;
                case "double":
                case "float":
                case "decimal":
                case "numeric":
                    rtnVal[0] = "double";
                    rtnVal[1] = "0";
                    break;
                #endregion
                #region 字符型
                case "nvarchar":
                case "varchar":
                    rtnVal[0] = "string";
                    rtnVal[1] = "string.Empty";
                    break;
                #endregion
                #region 时间
                case "datetime":
                case "date":
                    rtnVal[0] = "DateTime";
                    rtnVal[1] = "DateTime.Parse(\"1900-01-01\")";
                    break;
                #endregion
                #region 布尔型
                case "bit":
                    rtnVal[0] = "bool";
                    rtnVal[1] = "false";
                    break;
                #endregion
                default:
                    throw new Exception("未匹配到对应的数据类型，请核对工具代码。");
            }

            return rtnVal;
        }
        #endregion

        #region 获取数据结构DT
        private DataTable GetTableStructue(string tableName, bool getPK = false)
        {
            var tempDt = controllerSQLProvider.GetTableStructue(tableName);
            if (tempDt != null)
            {
                tempDt.TableName = tableName;
            }
            if (!getPK)
            {
                tempDt.Columns.Remove("主键");
            }
            return tempDt;
        }
        #endregion

        #region 获取数据结构对象
        private TableInfo GetTableInfo(string tableName)
        {
            var tempDt = controllerSQLProvider.GetTableStructue(tableName);

            var rtnObj = new TableInfo();
            rtnObj.TableName = tableName;
            rtnObj.DataStrucs = db.FindByDataTable<DataStruc>(tempDt);

            if (rtnObj.DataStrucs == null || rtnObj.DataStrucs.Count <= 0)
            {
                return null;
            }

            TransTableInfo(rtnObj);

            return rtnObj;
        }
        #endregion

        #endregion

        #region 切换数据库
        private void DataBaseChanged(object sender, EventArgs e)
        {
            textBox1.Text = "";
            checkedListBox1.DataSource = null;
            db.SetDataBaseName(comboBox1.Text);
        }
        #endregion

        #region 输入查询条件进行查询
        private void QueryTextChanged(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(textBox1.Text))
            {
                RetrieveData(false);
            }
        }
        #endregion

        #region 全选\全不选
        private void ChoseAllToolStripMenuItem1_Click(object sender, EventArgs e)
        {
            if (checkedListBox1.CheckedItems.Count == checkedListBox1.Items.Count)
            {
                for (int i = 0; i < checkedListBox1.Items.Count; i++)
                {
                    checkedListBox1.SetItemChecked(i, false);
                }
            }
            else
            {
                for (int i = 0; i < checkedListBox1.Items.Count; i++)
                {
                    checkedListBox1.SetItemChecked(i, true);
                }
            }
        }
        #endregion

        private delegate void BtnFunc();

        #region 异步执行功能并登记进度
        private void BackgroundWorkerDoWork(BtnFunc btbFunc)
        {
            if (backgroundWorker1.CancellationPending)
            {
                return;
            }

            try
            {
                btbFunc();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                backgroundWorker1.ReportProgress(0, "end");
            }
        }

        public void BgworkerProgessChanged(object sender, ProgressChangedEventArgs e)
        {
            this.progressBar1.Value = e.ProgressPercentage;

            var infoProgVal = Math.Round(Convert.ToDouble(e.ProgressPercentage) / 100, 2);
            label1.Text = infoProgVal + "%";

            if (e.UserState.ToString() == "start" && !progressBar1.Visible)
            {
                progressBar1.Show();
                label1.Show();
            }
            else if (e.UserState.ToString() == "end" && progressBar1.Visible)
            {
                progressBar1.Hide();
                label1.Hide();
            }
        }
        #endregion
    }
}
